 
 *Kayran and Nadler - Political Behavior*
 *Local alien enfranchisement and external efficacy perceptions: 
 * intended and unintended effects on non-citizens and citizens*
 
 ** Swiss Household Panel individual waves in wide form are merged and turned into long format**

**Creating the master data file at the individual level: 
 
cd "C:\Users\ENK\Desktop\Working directory"

***Converting SPSS municipal data to Stata files:

net from http://radyakin.org/transfer/usespss/beta
usespss "C:\Users\ENK\Desktop\Working directory\communes00.sav"
***********************************************************************

*** Merging each community file with the household waves:
use "C:\Users\ENK\Desktop\Working directory\shp99_h_user.dta" 
sort idhous99
merge m:1 idhous99 using communes99, nogen
save "C:\Users\ENK\Desktop\Working directory\shp99_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp00_h_user.dta" 
sort idhous00
merge m:1 idhous00 using communes00, nogen
save "C:\Users\ENK\Desktop\Working directory\shp00_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp01_h_user.dta" 
sort idhous01
merge m:1 idhous01 using communes01, nogen
save "C:\Users\ENK\Desktop\Working directory\shp01_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp02_h_user.dta" 
sort idhous02
merge m:1 idhous02 using communes02
rename _merge mergeinfo02
save "C:\Users\ENK\Desktop\Working directory\shp02_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp03_h_user.dta" 
sort idhous03
merge m:1 idhous03 using communes03, nogen
save "C:\Users\ENK\Desktop\Working directory\shp03_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp04_h_user.dta" 
sort idhous04
merge m:1 idhous04 using communes04
rename _merge mergeinfo04
save "C:\Users\ENK\Desktop\Working directory\shp04_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp05_h_user.dta" 
sort idhous05
merge m:1 idhous05 using communes05
rename _merge mergeinfo05
save "C:\Users\ENK\Desktop\Working directory\shp05_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp06_h_user.dta" 
sort idhous06
merge m:1 idhous06 using communes06, nogen
save "C:\Users\ENK\Desktop\Working directory\shp06_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp07_h_user.dta" 
sort idhous07
merge m:1 idhous07 using communes07, nogen
save "C:\Users\ENK\Desktop\Working directory\shp07_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp08_h_user.dta" 
sort idhous08
merge m:1 idhous08 using communes08, nogen
save "C:\Users\ENK\Desktop\Working directory\shp08_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp09_h_user.dta" 
sort idhous09
merge m:1 idhous09 using communes09, nogen
save "C:\Users\ENK\Desktop\Working directory\shp09_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp10_h_user.dta" 
sort idhous10
merge m:1 idhous10 using communes10, nogen
save "C:\Users\ENK\Desktop\Working directory\shp10_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp11_h_user.dta" 
sort idhous11
merge m:1 idhous11 using communes11, nogen
save "C:\Users\ENK\Desktop\Working directory\shp11_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp12_h_user.dta" 
sort idhous12
merge m:1 idhous12 using communes12, nogen
save "C:\Users\ENK\Desktop\Working directory\shp12_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp13_h_user.dta" 
sort idhous13
merge m:1 idhous13 using communes13, nogen
save "C:\Users\ENK\Desktop\Working directory\shp13_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp14_h_user.dta" 
sort idhous14
merge m:1 idhous14 using communes14, nogen
save "C:\Users\ENK\Desktop\Working directory\shp14_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp15_h_user.dta" 
sort idhous15
merge m:1 idhous15 using communes15
rename _merge mergeinfo15
save "C:\Users\ENK\Desktop\Working directory\shp15_h_user.dta", replace

use "C:\Users\ENK\Desktop\Working directory\shp16_h_user.dta" 
sort idhous16
merge m:1 idhous16 using communes16 
rename _merge mergeinfo16
save "C:\Users\ENK\Desktop\Working directory\shp16_h_user.dta", replace

**wave 17 communities data is missing
**********************************************************************************
***************************************************************************************
*** first merge all personal waves -- reshape to long
*** second merge all household waves -- reshape to long
*** merge personal and household waves by id household id and year 

*** demo for reshaping
drop if idpers >14676103
keep idpers idhous00 idhous01 idhous02  age02 age00 age01  sex00 sex01 sex02
reshape long  idhous age sex, i(idpers) j(year) string
reshape error
********************************************************
 

****Creating longitudinal person level data:

use "C:\Users\ENK\Desktop\Working directory\shp99_p_user.dta", clear
merge 1:1 idpers using "C:\Users\ENK\Desktop\Working directory\shp99_p_user.dta", nogen nol
local m=2  // local macro for a loop
foreach y in 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 {
	merge 1:1 idpers using "C:\Users\ENK\Desktop\Working directory\shp`y'_p_user", nogen
	local m = `m' + 1
}
save "C:\Users\ENK\Desktop\Working directory\shp_p_all.dta"


***Reshape from wide to long:

reshape long variables idhous status sex age relarp cohast idspou civsta maxcop ownkid wstat educat edcat edugr edgr isced edyear occupa is1maj is2maj cspmaj permit hab_ch nat_1_ nat_2_ nat_3_ reg_1_ reg_2_ re_3_ p05_  p06_ p29_ p30_ p31_ p32_ p33_ p34_ p07_ p08_ p09_ p10_ p11_ n42_ n43_ p01_ p02_ p03_ p04_ p26_ e16_ e17_ w92_ w101_ w232_ i01_ ptotn_ ptotg_ wyg_ wyn_ stpy_ stfy_ p12_ p36_ p13_ p37_ p14_ p38_ p15_ p39_ p40_ p41_ p17_ p43_ r04_ p19_ d160_ d161_ d162_ d163_ d164_ d165_ c160_ c161_ c162_ p85_ p82_ p81_ p86_ p72_ p45_ , i(idpers) j(year) string
   save "C:\Users\ENK\Desktop\Working directory\SHP_all_long_p.dta"     

****Creating longitudinal household level data:


use "C:\Users\ENK\Desktop\Working directory\shp99_h_user", clear
local m=2  // local macro for a loop
foreach y in 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16{
	rename idhous(##) idhous`y'
	merge m:1 idhous`y' using "C:\Users\ENK\Desktop\Working directory\shp`y'_h_user", nogen
	local m = `m' + 1
}
rename idhous16 idhous

save "C:\Users\ENK\Desktop\Working directory\shp_h_all.dta"

** Variables we need from the household data
ofs_99 canton99 region99 com1_99 com2_99 
hlingu99 refper99 rpspou99 hldtyp99
***Reshape from wide to long
reshape long variables ofs_ hlingu canton region com1_ com2_ refper rpspou hldtyp i55_ i56_ htyg_ htyn_ eqsg_ eqsn_, i(idhous) j(year) string

****Combine household and personal waves****

* Creating the year identifier:
generate syear = 0
replace syear = 1999 if year == "99"
replace syear = 2000 if year == "00"
replace syear = 2001 if year == "01"
replace syear = 2002 if year == "02"
replace syear = 2003 if year == "03"
replace syear = 2004 if year == "04"
replace syear = 2005 if year == "05"
replace syear = 2006 if year == "06"
replace syear = 2007 if year == "07"
replace syear = 2008 if year == "08"
replace syear = 2009 if year == "09"
replace syear = 2010 if year == "10"
replace syear = 2011 if year == "11"
replace syear = 2012 if year == "12"
replace syear = 2013 if year == "13"
replace syear = 2014 if year == "14"
replace syear = 2015 if year == "15"
replace syear = 2016 if year == "16"
tab syear

sort idhous syear
sort idpers idhous syear

use "C:\Users\ENK\Desktop\Working directory\SHP_all_long_p.dta"
sort idpers idhous syear

merge m:1 idhous syear using "C:\Users\ENK\Desktop\Working directory\SHP_h_all_long.dta"
rename _merge mergevar_p_to_h
save "C:\Users\ENK\Desktop\Working directory\shp_long_MASTER.dta"

********************************
**for weights:
foreach y in 99 00 01 02 03 {
	gen weih`y'tp_cor=weih`y'tp/nbpers`y'
	}
foreach y in 04 05 06 07 08 09 10 11 12 13  {
	gen wh`y't1p_cor=wh`y't1p/nbpers`y'
	}
	foreach y in  14 15 16 {
	gen wh`y't1p_cor=wh`y'css/nbpers`y'
	}

********************************************************

